package com.autonavi.yunda.yunji.core.utils;

import com.autonavi.yunda.yunji.common.exception.AmapException;
import com.autonavi.yunda.yunji.common.exception.AmapExceptionCode;
import com.autonavi.yunda.yunji.common.exception.AmapPreconditions;
import com.opencsv.CSVReader;
import com.opencsv.exceptions.CsvValidationException;
import org.apache.commons.lang3.StringUtils;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.springframework.web.multipart.MultipartFile;

import java.io.IOException;
import java.io.InputStream;
import java.io.InputStreamReader;
import java.text.SimpleDateFormat;
import java.util.*;
import java.util.stream.Collectors;

/**
 * @author sunzhenbang
 */
public class ExcelUtil {

    private static final String XLS = ".xls";
    private static final String XLSX = ".xlsx";
    private static final String CSV = ".csv";

    public static List<List<Map<String, Object>>> excelParseToList(MultipartFile file) {
        List<List<Map<String, Object>>> resultList = new ArrayList<>();
        if (Objects.isNull(file)) {
            throw new AmapException(AmapExceptionCode.COMMON_ILLEGAL_PARAM, "文件不存在");
        }
        String fileName = file.getOriginalFilename();
        AmapPreconditions.checkArgumentForUser(StringUtils.isNotBlank(file.getOriginalFilename()), "文件名为空");
        assert fileName != null;
        int index = fileName.lastIndexOf(".");
        AmapPreconditions.checkArgumentForUser(index != -1, "文件不带有扩展名，不属于Excel文件类型,fileName = " + fileName);
        String suffix = fileName.substring(index);
        if (CSV.equals(suffix)) {
            try (InputStream input = file.getInputStream()) {
                return readCSV(input);
            } catch (Exception e) {
                throw new AmapException(AmapExceptionCode.COMMON_SERVER_ERROR, "解析csv文件异常", e);
            }
        }
        try (InputStream input = file.getInputStream(); Workbook workbook = getWorkbookByExtensionName(input, suffix)) {
            FormulaEvaluator formulaEvaluator = workbook.getCreationHelper().createFormulaEvaluator();
            int totalSheetNum = workbook.getNumberOfSheets();
            Sheet sheet;
            for (int m = 0; m < totalSheetNum; m++) {
                sheet = workbook.getSheetAt(m);
                List<Map<String, Object>> sheetList = getDataBySheet(sheet, formulaEvaluator);
                resultList.add(sheetList);
            }
        } catch (Exception e) {
            throw new AmapException(AmapExceptionCode.COMMON_SERVER_ERROR, e);
        }
        return resultList;
    }

    private static Workbook getWorkbookByExtensionName(InputStream input, String suffix) throws Exception {
        Workbook wk;
        if (XLS.equals(suffix)) {
            wk = new HSSFWorkbook(input);
        } else if (XLSX.equals(suffix)) {
            wk = new XSSFWorkbook(input);
        } else {
            throw new AmapException(AmapExceptionCode.COMMON_ILLEGAL_PARAM, "文件扩展名不是excel文件格式(只能是.xls和.xlsx格式)");
        }
        return wk;
    }

    private static List<Map<String, Object>> getDataBySheet(Sheet sheet, FormulaEvaluator formulaEvaluator) {
        List<Map<String, Object>> sheetList = new ArrayList<>();
        if (Objects.isNull(sheet)) {
            return sheetList;
        }
        int totalRowNum = sheet.getLastRowNum() + 1;
        Map<String, String> headers = new LinkedHashMap<>(8);
        for (int i = 0; i < totalRowNum; i++) {
            Row row = sheet.getRow(i);
            if (row == null || StringUtils.isBlank(row.toString())) {
                continue;
            }
            int totalCellNum = row.getLastCellNum();
            Map<String, Object> rowMap = new LinkedHashMap<>(8);
            for (int j = 0; j < totalCellNum; j++) {
                Cell cell = row.getCell(j);
                CellValue cellValue = formulaEvaluator.evaluate(cell);
                Object result = getCellResultByCellType(cell, cellValue, formulaEvaluator);
                if (i == 0) {
                    headers.put("Col" + (j + 1), Objects.isNull(result) ? "Col" + (j + 1) : result.toString());
                    continue;
                }
                rowMap.put(headers.get("Col" + (j + 1)) != null ? headers.get("Col" + (j + 1)) : "Col" + (j + 1), result);
            }
            if (i > 0) {
                sheetList.add(rowMap);
            }
        }
        return sheetList;
    }

    private static Object getCellResultByCellType(Cell cell, CellValue cellValue, FormulaEvaluator formulaEvaluator) {
        if (Objects.isNull(cellValue) || Objects.isNull(cell)) {
            return null;
        }
        Object result;
        CellType cellType = cellValue.getCellType();
        DataFormatter dataFormatter = new DataFormatter();
        if (cellType == CellType.NUMERIC) {
            if (DateUtil.isCellDateFormatted(cell)) {
                SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");
                result = sdf.format(cell.getDateCellValue());
            } else {
                result = dataFormatter.formatCellValue(cell, formulaEvaluator);
            }
        } else if (cellType == CellType.STRING) {
            result = cellValue.getStringValue();
        } else if (cellType == CellType.BOOLEAN) {
            result = cellValue.getBooleanValue();
        } else {
            result = dataFormatter.formatCellValue(cell, formulaEvaluator);
        }
        return result;
    }

    public static List<List<Map<String, Object>>> readCSV(InputStream inputStream) {
        List<Map<String, Object>> mapList = new ArrayList<>(10);
        CSVReader reader;
        try {
            reader = new CSVReader(new InputStreamReader(inputStream));
            String[] columns;
            boolean firstLine = true;
            List<String> names = new ArrayList<>();
            while ((columns = reader.readNext()) != null) {
                if (firstLine) {
                    names = Arrays.asList(columns);
                    //utf-8-dom 格式的csv 起始字符是 ﻿
                    names = names.stream()
                            .map(n -> n.replace("\uFEFF", ""))
                            .collect(Collectors.toList());
                    firstLine = false;
                } else {
                    mapList.add(parseRowMap(names, Arrays.asList(columns)));
                }
            }
        } catch (IOException | CsvValidationException e) {
            throw new AmapException(AmapExceptionCode.COMMON_SERVER_ERROR, "解析csv文件异常", e);
        }
        return Collections.singletonList(mapList);
    }

    private static Map<String, Object> parseRowMap(List<String> names, List<String> values) {
        if (names.size() != values.size()) {
            throw new AmapException(AmapExceptionCode.COMMON_ILLEGAL_PARAM, "列名和参数数量不等");
        }
        Map<String, Object> map = new LinkedHashMap<>(8);
        for (int i = 0; i < names.size(); i++) {
            map.put(names.get(i), values.get(i));
        }
        return map;
    }


}

